﻿using ERPU8WebApi.Until;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;

namespace ERPU8WebApi.Dal
{
    //系统相关
    public class DALSys
    {
        SqlHelper s = new SqlHelper();
        //查询U8用户是否存在
        public bool GetUser(string userName)
        {
            string sql = string.Format(@"SELECT * FROM UA_User where cUser_Id='{0}' ", userName);
            DataTable dt = new DataTable();
            dt = s.ExecuteDataSetSql(sql, 0).Tables[0];
            if (dt.Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        //获取U8用户列表
        public DataTable GetUserList(string userId)
        {
            string sql = "SELECT cUser_Id,cUser_Name,cDept FROM UA_User where  ISNULL(nState,0)<>1  ";
            if (!string.IsNullOrEmpty(userId))
            {
                sql += " and cUser_Id='" + userId + "' ";
            }
            DataTable dt = new DataTable();
            dt = s.ExecuteDataSetSql(sql, 0).Tables[0];
            return dt;
        }
        //根据userid查username
        public string GetUserName(string cUser_Id)
        {
            string sql = string.Format(@"SELECT * FROM UA_User where cUser_Id='{0}' ", cUser_Id);
            DataTable dt = new DataTable();
            dt = s.ExecuteDataSetSql(sql, 0).Tables[0];
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0]["cUser_Name"].ToString();
            }
            else
            {
                return "接口专用";
            }
        }

        //查询编码分类规则
        public DataTable GetSupplierClassify(string keyword)
        {
            string sql = string.Format(@"select top 1 * from GradeDef where keyword='{0}' ", keyword);
            DataTable dt = new DataTable();
            dt = s.ExecuteDataSetSql(sql, 0).Tables[0];
            return dt;
        }

        //获取所属地区
        public DataTable GetDistrictClass(string cDCCode)
        {
            string sql = " select cDCCode,cDCName,iDCGrade,bDCEnd FROM DistrictClass where 1=1 ";
            if (!string.IsNullOrEmpty(cDCCode))
            {
                sql += " and cDCCode='" + cDCCode + "' ";
            }
            DataTable dt = new DataTable();
            dt = s.ExecuteDataSetSql(sql, 0).Tables[0];
            return dt;
        }

        //获取客户档案
        public DataTable GetCustomer(string cCusCode)
        {
            string sql = @" SELECT cCusCode,cCusName,cCusAbbName,c.cCCCode,cCusAddress,cc.cCCName FROM Customer c
                            LEFT JOIN dbo.CustomerClass cc ON cc.cCCCode = c.cCCCode where 1=1 ";
            if (!string.IsNullOrEmpty(cCusCode))
            {
                sql += " and c.cCusCode='" + cCusCode + "' ";
            }
            DataTable dt = new DataTable();
            dt = s.ExecuteDataSetSql(sql, 0).Tables[0];
            return dt;
        }
        //获取币种档案
        public DataTable GetForeigncurrency(string cexch_name)
        {
            string sql = "  SELECT cexch_name,cexch_code  FROM foreigncurrency   where 1=1 ";
            if (!string.IsNullOrEmpty(cexch_name))
            {
                sql += " and cexch_name='" + cexch_name + "' ";
            }
            DataTable dt = new DataTable();
            dt = s.ExecuteDataSetSql(sql, 0).Tables[0];
            return dt;
        }
        //获取银行档案
        public DataTable GetBank(string cVenBankCode)
        {
            string sql = "   SELECT cBankCode,cBankName FROM AA_Bank   where 1=1 ";
            if (!string.IsNullOrEmpty(cVenBankCode))
            {
                sql += " and cBankCode='" + cVenBankCode + "' ";
            }
            DataTable dt = new DataTable();
            dt = s.ExecuteDataSetSql(sql, 0).Tables[0];
            return dt;
        }
        //获取部门档案
        public DataTable GetDepartment(string cVenDepart)
        {
            string sql = "  SELECT  cDepCode,cDepName,cDepAddress,cDepFullName,iDepGrade  FROM Department    where 1=1 ";
            if (!string.IsNullOrEmpty(cVenDepart))
            {
                sql += " and cDepCode='" + cVenDepart + "' ";
            }
            DataTable dt = new DataTable();
            dt = s.ExecuteDataSetSql(sql, 0).Tables[0];
            return dt;
        }

        //获取业务员
        public DataTable GetPerson(string cPersonCode)
        {
            string sql =@"SELECT cPersonCode,cPersonName,p.cDepCode,d.cDepName from Person p 
                          LEFT JOIN dbo.Department d ON d.cDepCode = p.cDepCode 
                            where 1=1 ";
            if (!string.IsNullOrEmpty(cPersonCode))
            {
                sql += " and p.cPersonCode='" + cPersonCode + "' ";
            }
            DataTable dt = new DataTable();
            dt = s.ExecuteDataSetSql(sql, 0).Tables[0];
            return dt;
        }

        //获取结算方式
        public DataTable GetSettleStyle(string cSSCode)
        {
            string sql = "   SELECT cSSCode,cSSName ,iSSGrade ,bSSEnd FROM SettleStyle   where 1=1 ";
            if (!string.IsNullOrEmpty(cSSCode))
            {
                sql += " and cSSCode='" + cSSCode + "' ";
            }
            DataTable dt = new DataTable();
            dt = s.ExecuteDataSetSql(sql, 0).Tables[0];
            return dt;
        }

        //获取仓库
        public DataTable GetWarehouse(string cWhCode)
        {
            string sql = " SELECT cWhCode,cWhName from Warehouse  where 1=1 ";
            if (!string.IsNullOrEmpty(cWhCode))
            {
                sql += " and cWhCode='" + cWhCode + "' ";
            }
            DataTable dt = new DataTable();
            dt = s.ExecuteDataSetSql(sql, 0).Tables[0];
            return dt;
        }
    }
}